package com.kevin.utility.common;

import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * excel工具类
 * 
 * @author juntao.lai
 * @since 2015年1月30日
 *
 */
public class ExcelUtil {
    
	private static Logger logger = Logger.getLogger(ExcelUtil.class);
	
    public static HSSFWorkbook createWorkbook(){
        return new HSSFWorkbook();
    }
    
    /**
     * 操作Excel2007的版本，扩展名是.xlsx
     * @return
     */
    public static XSSFWorkbook createWorkbookXLSX(){
        return new XSSFWorkbook();
    }
    
    /**
     * 根据listmap生成Excel workbook
     * 最大生成65535行一个sheet,超出后生成新的sheet
     * 
     * @param dataMapList
     */
    public static void createSheet(HSSFWorkbook wb, List<Map> dataMapList, String originalSheetName){
        
        if (!CollectionUtil.hasValue(dataMapList) || null == dataMapList.get(0) 
                || !CollectionUtil.hasValue(dataMapList.get(0).keySet())){
            return;
        }
        
        Object[] headerArray = dataMapList.get(0).keySet().toArray();
        
        int sheetCount = (dataMapList.size() / 65534) + 1;
        
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            String sheetName = originalSheetName;
            if (sheetIndex > 0) {
                sheetName = originalSheetName + "(" + sheetIndex + ")";
            }
            
            HSSFSheet sheet = wb.createSheet(sheetName);
            HSSFRow rowHeader = sheet.createRow((int) 0);
            //表头风格
            HSSFCellStyle styleHeader = wb.createCellStyle();  
            styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
            
            for (int i=0; i<headerArray.length; i++){
                Object headerKey = headerArray[i];
                HSSFCell cell = rowHeader.createCell((short) i);  
                cell.setCellValue(StringUtil.isNullThen(headerKey));  
                cell.setCellStyle(styleHeader);
            }
            
            //数据风格
            HSSFCellStyle styleData = wb.createCellStyle();
            int beginIndex = (65534*sheetIndex);
            int endIndex = dataMapList.size() > (65534*(sheetIndex+1)) ? (65534*(sheetIndex+1)) : dataMapList.size();
            for (int i= beginIndex; i<endIndex; i++){
                HSSFRow rowData = sheet.createRow(i - beginIndex + 1);
                for (int j=0; j<headerArray.length; j++){
                    Object headerKey = headerArray[j];
                    Object value = dataMapList.get(i).get(headerKey);
                    
                    HSSFCell cell = rowData.createCell((short) j);  
                    cell.setCellValue(StringUtil.isNullThen(value));  
                    cell.setCellStyle(styleData);
                }
            }
        }
    }
    /**
     * 生成 2007版本的excel 即尾缀为XLSX 跟createSheet类型
     * @param wb
     * @param dataMapList
     * @param originalSheetName
     */
    public static void createSheetXLSX(XSSFWorkbook wb, List<Map> dataMapList, List<String> titles, String originalSheetName){
        
        if (!CollectionUtil.hasValue(dataMapList) || null == dataMapList.get(0) 
                || !CollectionUtil.hasValue(dataMapList.get(0).keySet())){
            return;
        }
        
        Object[] headerArray = null;
        if(titles != null && titles.size() > 0){
        	headerArray = titles.toArray();
        }else{
        	headerArray = dataMapList.get(0).keySet().toArray();
        }
        int sheetCount = (dataMapList.size() / 65534) + 1;
        
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            String sheetName = originalSheetName;
            if (sheetIndex > 0) {
                sheetName = originalSheetName + "(" + sheetIndex + ")";
            }
            
            XSSFSheet sheet = wb.createSheet(sheetName);
            XSSFRow rowHeader = sheet.createRow((int) 0);
            //表头风格
            XSSFCellStyle styleHeader = wb.createCellStyle();  
            styleHeader.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
            
            for (int i=0; i<headerArray.length; i++){
                Object headerKey = headerArray[i];
                XSSFCell cell = rowHeader.createCell((short) i);  
                cell.setCellValue(StringUtil.isNullThen(headerKey));  
                cell.setCellStyle(styleHeader);
            }
            
            //数据风格
            XSSFCellStyle styleData = wb.createCellStyle();
            int beginIndex = (65534*sheetIndex);
            int endIndex = dataMapList.size() > (65534*(sheetIndex+1)) ? (65534*(sheetIndex+1)) : dataMapList.size();
            for (int i= beginIndex; i<endIndex; i++){
            	XSSFRow rowData = sheet.createRow(i - beginIndex + 1);
                for (int j=0; j<headerArray.length; j++){
                    Object headerKey = headerArray[j];
                    Object value = dataMapList.get(i).get(headerKey);
                    
                    XSSFCell cell = rowData.createCell((short) j);  
                    cell.setCellValue(StringUtil.isNullThen(value));  
                    cell.setCellStyle(styleData);
                }
            }
        }
    }
    
    /**
     * 生成 2007版本的excel 即尾缀为XLSX 跟createSheet类型
     * @param wb
     * @param dataMapList
     * @param originalSheetName
     */
    public static void createSheetXLSX(XSSFWorkbook wb, List<Map> dataMapList, String originalSheetName){
        
        if (!CollectionUtil.hasValue(dataMapList) || null == dataMapList.get(0) 
                || !CollectionUtil.hasValue(dataMapList.get(0).keySet())){
            return;
        }
        
        Object[] headerArray = dataMapList.get(0).keySet().toArray();
        
        int sheetCount = (dataMapList.size() / 65534) + 1;
        
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {
            String sheetName = originalSheetName;
            if (sheetIndex > 0) {
                sheetName = originalSheetName + "(" + sheetIndex + ")";
            }
            
            XSSFSheet sheet = wb.createSheet(sheetName);
            XSSFRow rowHeader = sheet.createRow((int) 0);
            //表头风格
            XSSFCellStyle styleHeader = wb.createCellStyle();  
            styleHeader.setAlignment(XSSFCellStyle.ALIGN_CENTER);  
            
            for (int i=0; i<headerArray.length; i++){
                Object headerKey = headerArray[i];
                XSSFCell cell = rowHeader.createCell((short) i);  
                cell.setCellValue(StringUtil.isNullThen(headerKey));  
                cell.setCellStyle(styleHeader);
            }
            
            //数据风格
            XSSFCellStyle styleData = wb.createCellStyle();
            int beginIndex = (65534*sheetIndex);
            int endIndex = dataMapList.size() > (65534*(sheetIndex+1)) ? (65534*(sheetIndex+1)) : dataMapList.size();
            for (int i= beginIndex; i<endIndex; i++){
                XSSFRow rowData = sheet.createRow(i - beginIndex + 1);
                for (int j=0; j<headerArray.length; j++){
                    Object headerKey = headerArray[j];
                    Object value = dataMapList.get(i).get(headerKey);
                    
                    XSSFCell cell = rowData.createCell((short) j);  
                    cell.setCellValue(StringUtil.isNullThen(value));  
                    cell.setCellStyle(styleData);
                }
            }
        }
    }
    
    /**
     * 导出excel文件到服务器路径
     * 
     * @param wb
     * @param path
     * @throws IOException
     */
    public static void exportExcelFile(HSSFWorkbook wb, String path) throws IOException{
        if (null == wb){
            return;
        }
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(path);
            wb.write(fout);
        } catch (IOException ex){
            throw ex;
        } finally {
            if (null != fout){
                fout.close();
            }
        }
    }
    
    /**
     * 导出Excel2007的版本，扩展名是.xlsx
     * @param wb
     * @param path
     * @throws IOException
     */
    public static void exportExcelFileXLSX(XSSFWorkbook wb, String path) throws IOException{
        if (null == wb){
            return;
        }
        FileOutputStream fout = null;
        try {
            fout = new FileOutputStream(path);
            wb.write(fout);
        } catch (IOException ex){
            throw ex;
        } finally {
            if (null != fout){
                fout.close();
            }
        }
    }
    /**
     * Title: export
     * Description:
     * Created On: 2015年5月20日 上午9:51:11
     * @author kevin02.liu 
     * @param wb
     * @param response
     * @throws IOException 
     */
    public static void export(XSSFWorkbook wb, String fileName,HttpServletRequest reqeust ,HttpServletResponse response) throws IOException{
        if (null == wb){
            return;
        }
        ServletOutputStream outputStream = response.getOutputStream();

        response.setContentType("application/xlsx");  
        response.setCharacterEncoding("UTF-8");
        String agent = reqeust.getHeader("user-agent");
        if(agent.toUpperCase().contains("FIREFOX")){
        	fileName = new String(fileName.getBytes("GB2312"),"ISO-8859-1");//适应Firefox的编码处理
        }else{
        	fileName =URLEncoder.encode(fileName, "UTF-8");
        }
        response.setHeader("Content-Disposition", "inline; filename=\""   + fileName + ".xlsx\"");  
        try {
            wb.write(outputStream);
        } catch (IOException ex){
            throw ex;
        } finally {
            if (null != outputStream){
            	outputStream.close();
            }
        }
    }
    
    
    
    /**
     * Title: export
     * Description: 生成workBook，然后直接导出
     * Created On: 2015年5月20日 上午9:52:28
     * @author kevin02.liu 
     * @param dataMapList
     * @param originalSheetName
     * @param response
     * @throws IOException 
     */
    public static void export(List<Map> dataMapList, String originalSheetName,HttpServletRequest request,HttpServletResponse response) throws IOException{
    	//生成wb
    	XSSFWorkbook createWorkbookXLSX = createWorkbookXLSX();
    	//写数据
    	createSheetXLSX(createWorkbookXLSX, dataMapList, originalSheetName);
    	//导出
		export(createWorkbookXLSX,originalSheetName,request,response);
    }
    
    public static void export(List<Map> dataMapList,List<String> titles, String originalSheetName,HttpServletRequest request,HttpServletResponse response) throws IOException{
    	//生成wb
    	XSSFWorkbook createWorkbookXLSX = createWorkbookXLSX();
    	//写数据
    	createSheetXLSX(createWorkbookXLSX, dataMapList, titles, originalSheetName);
    	//导出
		export(createWorkbookXLSX,originalSheetName,request,response);
    }
}
